﻿Partial Public Class WPF_UC_Forecast

    'This variable will be used to improve performance and prevent additional 
    ' Salesperson lookups when a sales person is initially selected in the task pane.
    Private currentlySelectedSalesRep As Integer = 0

    Private Sub WPF_UC_Forecast_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded
        Globals.Ribbons.Ribbon1.PerformLayout()
    End Sub

    Public Sub LoadData()
        Globals.ThisWorkbook.AdvWorksDC = New AdventureWorksVSTO(My.Settings.AdvWorksDBVSTO)
        If Globals.ThisWorkbook.SalesEmployeeID > 0 Then
            'This is a previously opened document not a new document.
            'Need to hide the task pane because it has no more use - the forecast has been assigned to someone
            ' and the companies selected.
            Globals.ThisWorkbook.TaskPaneVisible = False
            'Get the current data
            Dim territory = From terrtbl In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesTerritory _
                            Select terrtbl.Name, terrtbl.CountryRegionCode, terrtbl.Group, terrtbl.TerritoryID _
                            Where TerritoryID = Globals.ThisWorkbook.TerritoryID
            ComboBoxGroup.Items.Add(territory.First.Group)
            ComboBoxGroup.SelectedItem = territory.First.Group
            ComboBoxTerritory.Items.Add(territory.First.Name)
            ComboBoxTerritory.SelectedItem = territory.First.Name
            ComboBoxRegion.Items.Add(territory.First.CountryRegionCode)
            ComboBoxRegion.SelectedItem = territory.First.CountryRegionCode
            Dim salesrep = From persons In Globals.ThisWorkbook.AdvWorksDC.Person_Contact _
                           Select persons.FirstName, persons.LastName, persons.ContactID _
                           Where ContactID = Globals.ThisWorkbook.SalesContactID
            Me.ComboBoxSalesPerson.Items.Add(salesrep.First.FirstName + " " + salesrep.First.LastName)
            Me.ComboBoxSalesPerson.SelectedItem = salesrep.First.FirstName + " " + salesrep.First.LastName
            ListBoxCompanies.SelectionMode = Windows.Controls.SelectionMode.Multiple
            Dim forecastcompanies = From forecastEst In Globals.ThisWorkbook.AdvWorksDC.Sales_ForecastEstimates _
                                    Join customer In Globals.ThisWorkbook.AdvWorksDC.Sales_Customer _
                                    On customer.CustomerID Equals forecastEst.CompanyID _
                                    Select customer.CustomerID, customer.CustomerName, forecastEst.ForeCastID, forecastEst.CompanyID, customer.TerritoryID _
                                    Where ForeCastID = Globals.ThisWorkbook.ForecastID
            For Each forecastquery In forecastcompanies
                If Not Globals.ThisWorkbook.CompanyIDs.ContainsKey(forecastquery.CustomerName) Then
                    Globals.ThisWorkbook.CompanyIDs.Add(forecastquery.CustomerName, forecastquery.CompanyID)
                    ListBoxCompanies.Items.Add(forecastquery.CustomerName)
                    ListBoxCompanies.SelectedItems.Add(forecastquery.CustomerName)
                End If
            Next
            DeactivateControls()
        Else
            Globals.ThisWorkbook.TaskPaneVisible = True
            Dim groups = From regions In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesTerritory Select groupname = regions.Group Distinct
            For Each groupname In groups
                Me.ComboBoxGroup.Items.Add(groupname.ToString())
            Next
            Me.ComboBoxGroup.SelectedIndex = 0
        End If
        ListBoxCompanies.SelectionMode = Windows.Controls.SelectionMode.Multiple
    End Sub

    Private Sub ComboBoxGroup_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles ComboBoxGroup.SelectionChanged
        Dim country = From regions In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesTerritory Select regions.Group, regions.CountryRegionCode Distinct Where Group = ComboBoxGroup.SelectedValue.ToString
        ComboBoxRegion.Items.Clear()
        For Each abbrev In country
            Me.ComboBoxRegion.Items.Add(abbrev.CountryRegionCode)
        Next
        Me.ComboBoxRegion.SelectedIndex = 0
    End Sub

    Public Sub DeactivateControls()
        ' Disable everything.
        ComboBoxGroup.IsEnabled = False
        ComboBoxTerritory.IsEnabled = False
        ComboBoxRegion.IsEnabled = False
        ComboBoxSalesPerson.IsEnabled = False
        ListBoxCompanies.IsEnabled = False
        ' Leave the button enabled to reload the data.
        ButtonLoad.Content = "Reload Sales Data"
        '        ButtonLoad.IsEnabled = False
    End Sub

    Private Sub ComboBoxRegion_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles ComboBoxRegion.SelectionChanged
        If ComboBoxRegion.SelectedIndex > -1 And Globals.ThisWorkbook.ForecastID = 0 Then
            Dim territory = From regions In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesTerritory Select regions.CountryRegionCode, regions.Name Distinct Where CountryRegionCode = ComboBoxRegion.SelectedValue.ToString
            ComboBoxTerritory.Items.Clear()
            For Each territoryName In territory
                Me.ComboBoxTerritory.Items.Add(territoryName.Name)
            Next
            Me.ComboBoxTerritory.SelectedIndex = 0
        End If
    End Sub

    Private Sub ComboBoxTerritory_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles ComboBoxTerritory.SelectionChanged
        If ComboBoxTerritory.SelectedIndex > -1 And Globals.ThisWorkbook.ForecastID = 0 Then
            Dim salesreps = From salespeople In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesPerson _
                     Join territory In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesTerritory _
                     On territory.TerritoryID Equals salespeople.TerritoryID _
                     Join employee In Globals.ThisWorkbook.AdvWorksDC.HumanResources_Employee _
                     On employee.EmployeeID Equals salespeople.SalesPersonID _
                     Join contact In Globals.ThisWorkbook.AdvWorksDC.Person_Contact _
                     On contact.ContactID Equals employee.ContactID _
                     Select contact.FirstName, contact.LastName, qryTerrName = territory.Name, _
                     territory.TerritoryID, contact.ContactID, salespeople.SalesPersonID _
                     Where (qryTerrName = ComboBoxTerritory.SelectedValue.ToString)
            ComboBoxSalesPerson.Items.Clear()
            For Each salesrep In salesreps
                Me.ComboBoxSalesPerson.Items.Add(salesrep.FirstName + " " + salesrep.LastName)
            Next
            Globals.ThisWorkbook.TerritoryID = salesreps.First.TerritoryID
            Globals.ThisWorkbook.SalesContactID = salesreps.First.ContactID
            Globals.ThisWorkbook.SalesEmployeeID = salesreps.First.SalesPersonID
            currentlySelectedSalesRep = 0
            ComboBoxSalesPerson.SelectedIndex = 0
            ' retrieve companies and related information
            GetSalesPersonData(salesreps.First.SalesPersonID, salesreps.First.TerritoryID)
        End If
    End Sub

    Private Sub ComboBoxSalesPerson_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles ComboBoxSalesPerson.SelectionChanged
        ' In this case we know that having more then one salesperson is the exception vs. the rule
        ' so the code is not optimized for multiple sales people in a region.
        If ComboBoxSalesPerson.SelectedIndex > -1 AndAlso ComboBoxSalesPerson.SelectedIndex <> currentlySelectedSalesRep Then
            Dim SalesRepName() = ComboBoxSalesPerson.SelectedValue.ToString().Split(" "c)
            ' First names are limited to a single word/name - however some people may have a space
            ' in their last name...
            If SalesRepName.Count > 2 Then
                For i = 2 To SalesRepName.Count - 1
                    SalesRepName(1) = SalesRepName(1) + " " + SalesRepName(i)
                Next
            End If
            ' We have selected a different sales person from the list of available sales people
            ' in the current region - find the details of the newly selected sales person.
            Dim salesreps = From salespeople In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesPerson _
                 Join employee In Globals.ThisWorkbook.AdvWorksDC.HumanResources_Employee _
                 On employee.EmployeeID Equals salespeople.SalesPersonID _
                 Join contact In Globals.ThisWorkbook.AdvWorksDC.Person_Contact _
                 On contact.ContactID Equals employee.ContactID _
                 Select contact.FirstName, contact.LastName, _
                 salespeople.TerritoryID, contact.ContactID, salespeople.SalesPersonID _
                 Where (TerritoryID = Globals.ThisWorkbook.TerritoryID And FirstName = SalesRepName(0) And LastName = SalesRepName(1))

            Globals.ThisWorkbook.TerritoryID = salesreps.First.TerritoryID.Value
            Globals.ThisWorkbook.SalesContactID = salesreps.First.ContactID
            Globals.ThisWorkbook.SalesEmployeeID = salesreps.First.SalesPersonID
            ' retrieve companies and related sales information
            GetSalesPersonData(salesreps.First.SalesPersonID, salesreps.First.TerritoryID.Value)

        End If
    End Sub

    Private Sub ListBoxCompanies_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles ListBoxCompanies.SelectionChanged
        'Limit the selections to 5 companies at a time.
        If ListBoxCompanies.SelectedItems.Count > 5 Then
            ListBoxCompanies.SelectedItems.RemoveAt(0)
        End If
    End Sub

    Private Sub ButtonLoad_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles ButtonLoad.Click
        If ListBoxCompanies.SelectedItems.Count = 0 Then
            MessageBox.Show("Please select one to five companies for forecasting.")
            Return
        End If

        If Globals.ThisWorkbook.ForecastID = 0 Then
            'Set the Manager's ID
            Globals.ThisWorkbook.ManagerEmployeeID = (From employee In Globals.ThisWorkbook.AdvWorksDC.HumanResources_Employee _
                                                      Select employee.EmployeeID, employee.ManagerID _
                                                      Where EmployeeID = Globals.ThisWorkbook.SalesEmployeeID).First.ManagerID.Value
            'Get the employee email
            Globals.ThisWorkbook.SalesContactEmail = (From contact In Globals.ThisWorkbook.AdvWorksDC.Person_Contact _
                                                      Select contact.ContactID, contact.EmailAddress _
                                                      Where ContactID = Globals.ThisWorkbook.SalesContactID).First.EmailAddress
            'Set the date
            Globals.ThisWorkbook.ForecastDate = Today()

            'Get the Manager's name and email
            Dim manager = From employee In Globals.ThisWorkbook.AdvWorksDC.HumanResources_Employee _
                          Join contact In Globals.ThisWorkbook.AdvWorksDC.Person_Contact _
                          On employee.ContactID Equals contact.ContactID _
                          Select employee.EmployeeID, contact.EmailAddress, contact.FirstName, contact.LastName _
                          Where EmployeeID = Globals.ThisWorkbook.ManagerEmployeeID

            Globals.ThisWorkbook.ManagerContactEmail = manager.First.EmailAddress
            Globals.Sheet1.NamedRangeManagerName.Value2 = manager.First.FirstName + " " + manager.First.LastName

            'Update the other named ranges based on the combo boxes.
            Globals.Sheet1.NamedRangeSalesRepName.Value2 = ComboBoxSalesPerson.SelectedValue
            Globals.Sheet1.NamedRangeTerritoryName.Value2 = ComboBoxTerritory.SelectedValue
            Globals.Sheet1.NamedRangeForecastDate.Value2 = Today()
            Globals.Sheet1.NamedRangeGroupName.Value2 = ComboBoxGroup.SelectedValue
            Globals.Sheet1.NamedRangeManagerName.Locked = True
            Globals.Sheet1.NamedRangeForecastDate.Locked = True
            Globals.Sheet1.NamedRangeTerritoryName.Locked = True
            Globals.Sheet1.NamedRangeSalesRepName.Locked = True
            Globals.Sheet1.NamedRangeGroupName.Locked = True
        End If

        'For demonstration purposes add dummy comments...
        Dim SysArray = CType(Globals.Sheet1.NamedRangeForecastComments.get_Value, System.Array)
        If SysArray(1, 1) Is Nothing OrElse String.IsNullOrEmpty(SysArray(1, 1).ToString) Then
            Globals.Sheet1.NamedRangeForecastComments.Value2 = "Place comments related to these projections here."
        End If

        ' Get the companies.
        Dim customerNm As String
        Globals.ThisWorkbook.CompanyIDs.Clear()

        Dim forecastList = Globals.Sheet1.ListForecast.Range
        Dim salesList = Globals.Sheet1.ListForecast.Range

        ' compute display pivot - since the dates won't come back in a predictable
        ' order figure out the adjustment values to place the data in the correct columns
        Dim startMonth As Integer = Globals.ThisWorkbook.SalesStartDate.Month
        'Load the data
        Dim rowIndex As Integer = 0
        Globals.Sheet1.ListForecast.DataSource = Globals.ThisWorkbook.blankDataTable
        Globals.Sheet1.ListSales.SetDataBinding(Globals.ThisWorkbook.blankDataTable)
        For Each companyName In ListBoxCompanies.SelectedItems
            customerNm = companyName.ToString
            If Not Globals.ThisWorkbook.salesDataTable.Rows(rowIndex)(0) Is customerNm Then
                Globals.ThisWorkbook.salesDataTable.Rows(rowIndex)(0) = customerNm
                If Not Globals.ThisWorkbook.salesDataTable.Rows(rowIndex)(0) Is "-" Then
                    'In this case a different company data is being loaded - since no sales doesn't return
                    ' a result need to re-zero that row
                    For i = 1 To 12
                        Globals.ThisWorkbook.salesDataTable.Rows(rowIndex)(i) = 0
                    Next
                End If
            End If
            Dim companyid = (From customer In Globals.ThisWorkbook.AdvWorksDC.Sales_Customer _
                             Select customer.CustomerID, customer.CustomerName, customer.TerritoryID _
                             Where CustomerName = customerNm).First.CustomerID
            Globals.ThisWorkbook.CompanyIDs.Add(customerNm, companyid)
            Dim currentSales = Globals.ThisWorkbook.AdvWorksDC.UspGetSalesDataByMonths( _
                            Globals.ThisWorkbook.SalesEmployeeID, companyid, Globals.ThisWorkbook.SalesStartDate, _
                            Globals.ThisWorkbook.SalesEndDate)
            For Each companyData As UspGetSalesDataByMonthsResult In currentSales
                If companyData.Month IsNot Nothing Then
                    Dim i As Integer = companyData.Month.Value
                    If i < startMonth Then
                        i = i + (12 - (startMonth - 1))
                    Else
                        i = i - (startMonth - 1)
                    End If
                    Globals.ThisWorkbook.salesDataTable.Rows(rowIndex)(i) = companyData.ActualSales
                End If
            Next

            GenerateForecast(customerNm, rowIndex, 1.1)
            rowIndex += 1
        Next

        ClearEmptyRows(rowIndex)
        'Bind the updated tables (note you can leave the tables bound - but doing so negatively impacts perf.)
        'Globals.ThisWorkbook.salesDataTable.Columns(0).ReadOnly = True
        Globals.Sheet1.ListForecast.DataSource = Globals.ThisWorkbook.forecastDataTable
        Globals.Sheet1.ListSales.SetDataBinding(Globals.ThisWorkbook.salesDataTable)
        Globals.ThisWorkbook.TaskPaneVisible = False
    End Sub

    Friend Sub GenerateForecast(ByVal CustomerName As String, ByVal row As Integer, ByVal ForecastIncrease As Double)
        ' Get the forecast data.
        ' make the next 12 months available
        Globals.ThisWorkbook.forecastDataTable.Rows(row)(0) = CustomerName
        For i = 1 To 12
            Dim salesNum As Decimal = 0
            Decimal.TryParse(Globals.ThisWorkbook.salesDataTable.Rows(row)(i).ToString(), salesNum)
            salesNum = salesNum * CType(ForecastIncrease, Decimal)
            If salesNum = 0 Then
                salesNum = 100D
            End If
            Globals.ThisWorkbook.forecastDataTable.Rows(row)(i) = salesNum
        Next
    End Sub

    Private Sub GetSalesPersonData(ByVal salesID As Integer, ByVal locationID As Integer)
        Dim companyList = From orders In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesOrderHeader _
                          Join customers In Globals.ThisWorkbook.AdvWorksDC.Sales_Customer _
                          On orders.CustomerID Equals customers.CustomerID _
                          Select orders.SalesPersonID, orders.TerritoryID, _
                          customers.CustomerName Distinct _
                          Where (SalesPersonID = salesID And TerritoryID = locationID) _
                          Order By CustomerName
        ListBoxCompanies.Items.Clear()
        For Each company In companyList
            ListBoxCompanies.Items.Add(company.CustomerName)
        Next


        Dim salesPersonData = From salesdata In Globals.ThisWorkbook.AdvWorksDC.Sales_SalesPerson _
                              Select salesdata.SalesPersonID, salesdata.SalesQuota, salesdata.Bonus, _
                              salesdata.CommissionPct, salesdata.SalesYTD, salesdata.SalesLastYear _
                              Where SalesPersonID = salesID
        LabelBonus.Content = salesPersonData.First.Bonus.ToString("c")
        LabelCommissionPct.Content = salesPersonData.First.CommissionPct.ToString("p")
        LabelSalesLastYear.Content = salesPersonData.First.SalesLastYear.ToString("c")
        LabelSalesQuota.Content = CType(salesPersonData.First.SalesQuota, Decimal).ToString("c")
        LabelSalesYTD.Content = salesPersonData.First.SalesYTD.ToString("c")
    End Sub

    Private Sub ClearEmptyRows(ByVal startIndex As Integer)
        For i = startIndex To 4
            If Not (Globals.ThisWorkbook.salesDataTable.Rows(i)(0) Is "-") Then
                Globals.ThisWorkbook.salesDataTable.Rows(i)(0) = "-"
                'Globals.ThisWorkbook.salesDataTable.AcceptChanges()
                Globals.ThisWorkbook.forecastDataTable.Rows(i)(0) = "-"
                For j = 1 To 12
                    Globals.ThisWorkbook.salesDataTable.Rows(i)(j) = 0
                    'Globals.ThisWorkbook.salesDataTable.AcceptChanges()
                    Globals.ThisWorkbook.forecastDataTable.Rows(i)(j) = 0
                Next
            End If
        Next
    End Sub


End Class
